
-- Project: Lazarus
-- Table and Relationship creation scripts.
      
      -- Table Creation

SET ANSI_NULLS ON 
GO


/*
Table Creation -- ExpertiseLevel

Specifies the levels of expertise you may have in a current technology.
*/
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.type = 'U' AND syso.name = 'tblExpertiseLevel' ) )
CREATE TABLE dbo.[tblExpertiseLevel] 
(
   [Id] INT IDENTITY( 1, 1 ) NOT NULL,
   [Rank] INT NOT NULL,
   [Title] VARCHAR( 50 ),
   [Description] TEXT,
   [Created] DATETIME NOT NULL,
   [Modified] DATETIME NOT NULL
   
)

/*
Table Creation -- Technology

Specifies a technology for which you are tracking competency.
*/
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.type = 'U' AND syso.name = 'tblTechnology' ) )
CREATE TABLE dbo.[tblTechnology] 
(
   [Id] INT IDENTITY( 1, 1 ) NOT NULL,
   [Title] VARCHAR( 50 ),
   [Description] TEXT,
   [ExpertiseLevelId] INT, /* Specifies the level of Expertise you have in the current technology. */
   [Created] DATETIME NOT NULL,
   [Modified] DATETIME NOT NULL
   
)

/*
Table Creation -- GoalStatus

Specifies a technology for which you are tracking competency.
*/
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.type = 'U' AND syso.name = 'tblGoalStatus' ) )
CREATE TABLE dbo.[tblGoalStatus] 
(
   [Id] INT IDENTITY( 1, 1 ) NOT NULL,
   [Title] VARCHAR( 50 ),
   [Description] TEXT,
   [Created] DATETIME NOT NULL,
   [Modified] DATETIME NOT NULL
   
)

/*
Table Creation -- Goal

Defines a goal or milestone.  This may involve one or more of your chosen Technology Focuses.
*/
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.type = 'U' AND syso.name = 'tblGoal' ) )
CREATE TABLE dbo.[tblGoal] 
(
   [Id] INT IDENTITY( 1, 1 ) NOT NULL,
   [Title] VARCHAR( 50 ) NOT NULL, /* Specifies the title of the goal.  This should be a terse description. */
   [StatusId] INT NOT NULL, /* Specifies the status of the goal.  The identifies whether or not you have reached your milestone or goal in totality, or are working towards it. */
   [Created] DATETIME NOT NULL,
   [Modified] DATETIME NOT NULL
   
)

/*
Table Creation -- GoalRequiredExpertise

Defines which Technology Focuses are is required to satisfy your various goals.
*/
IF ( NOT EXISTS( SELECT syso.id FROM sysobjects syso WHERE syso.type = 'U' AND syso.name = 'tblGoalRequiredExpertise' ) )
CREATE TABLE dbo.[tblGoalRequiredExpertise] 
(
   [Id] INT IDENTITY( 1, 1 ) NOT NULL,
   [GoalId] INT NOT NULL,
   [TechnologyId] INT NOT NULL,
   [Created] DATETIME NOT NULL,
   [Modified] DATETIME NOT NULL
   
)

      
GO

	-- PK Index Creation
IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = 'tblExpertiseLevel' ) AND ( syso2.name = 'PK_ExpertiseLevel' ) ) )
   ALTER TABLE [tblExpertiseLevel] ADD CONSTRAINT [PK_ExpertiseLevel] PRIMARY KEY ( [Id] )
GO

IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = 'tblTechnology' ) AND ( syso2.name = 'PK_Technology' ) ) )
   ALTER TABLE [tblTechnology] ADD CONSTRAINT [PK_Technology] PRIMARY KEY ( [Id] )
GO

IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = 'tblGoalStatus' ) AND ( syso2.name = 'PK_GoalStatus' ) ) )
   ALTER TABLE [tblGoalStatus] ADD CONSTRAINT [PK_GoalStatus] PRIMARY KEY ( [Id] )
GO

IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = 'tblGoal' ) AND ( syso2.name = 'PK_Goal' ) ) )
   ALTER TABLE [tblGoal] ADD CONSTRAINT [PK_Goal] PRIMARY KEY ( [Id] )
GO

IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = 'tblGoalRequiredExpertise' ) AND ( syso2.name = 'PK_GoalRequiredExpertise' ) ) )
   ALTER TABLE [tblGoalRequiredExpertise] ADD CONSTRAINT [PK_GoalRequiredExpertise] PRIMARY KEY ( [Id] )
GO

-- Default Creation
   
SET ANSI_NULLS OFF
GO   
   